IF EXISTS(SELECT name FROM sys.databases
	WHERE name = 'APMAConfiguration')
	DROP DATABASE APMAConfiguration
GO

CREATE DATABASE APMAConfiguration
GO

USE APMAConfiguration
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('Users') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Users
GO

CREATE TABLE Users
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50),
	[Password] NVARCHAR(50),
	[Email] NVARCHAR(50),
	[Access_id] INT
)
go

INSERT INTO Users(Name, [Password], [Access_id]) VALUES ('Vasya', SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', '123')), 3, 32), 1);
GO

INSERT INTO Users(Name, [Password], [Access_id]) VALUES ('Petya', SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', '111')), 3, 32), 2);
GO

IF OBJECT_ID('validate_user', 'p') IS NOT NULL 
    DROP PROCEDURE validate_user;
GO

IF OBJECT_ID ( 'get_users', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_users;
GO

CREATE PROCEDURE get_users
AS
	SELECT Id, Name, Email, Password, Access_id FROM Users
GO

CREATE PROCEDURE validate_user
    @Login NVARCHAR(50), 
    @Password NVARCHAR(50)
AS
	DECLARE @ReturnValue INT
	SELECT @ReturnValue = Access_id FROM Users WHERE [Name] = @Login AND [Password] = @Password
	RETURN @ReturnValue
GO

IF OBJECT_ID('get_userid', 'p') IS NOT NULL 
    DROP PROCEDURE get_userid;
GO

CREATE PROCEDURE get_userid
    @Login NVARCHAR(50)
AS
	DECLARE @ReturnValue INT
	SELECT @ReturnValue = id FROM Users WHERE [Name] = @Login
	RETURN @ReturnValue
GO

IF OBJECT_ID('create_user', 'p') IS NOT NULL 
    DROP PROCEDURE create_user;
GO

CREATE PROCEDURE create_user
    @Login NVARCHAR(50), 
    @Password NVARCHAR(50),
    @Email NVARCHAR(50),
	@Access_id INT
AS
	INSERT INTO Users([Name], [Password], [Email], [Access_id]) VALUES(@Login, @Password, @Email, @Access_id)
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('update_user_with_pw', 'p') IS NOT NULL 
    DROP PROCEDURE update_user_with_pw;
GO

CREATE PROCEDURE update_user_with_pw
    @Id INT,
	@Login NVARCHAR(50),
	@Password NVARCHAR(50),
	@Email NVARCHAR(50),
	@Access_id INT
AS
	UPDATE Users SET 
		[Name] = @Login, 
		[Password] = @Password, 
		[Email] = @Email, 
		[Access_id] = @Access_id
	WHERE Id = @Id
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('update_user', 'p') IS NOT NULL 
    DROP PROCEDURE update_user;
GO

CREATE PROCEDURE update_user
    @Id INT,
	@Login NVARCHAR(50),
	@Email NVARCHAR(50),
	@Access_id INT
AS
	UPDATE Users SET 
		[Name] = @Login, 
		[Email] = @Email, 
		[Access_id] = @Access_id
	WHERE Id = @Id
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('remove_user', 'p') IS NOT NULL 
    DROP PROCEDURE remove_user;
GO

CREATE PROCEDURE remove_user
    @Id INT
AS
	DECLARE @row_count INT

	DELETE FROM Users WHERE Id = @Id
	SET @row_count = @@ROWCOUNT;
	
	UPDATE Logs SET [User_id] = NULL WHERE [User_id] = @Id
	UPDATE Tasks SET [Owner_id] = NULL WHERE [Owner_id] = @Id
	UPDATE UserStories SET [Owner_id] = NULL WHERE [Owner_id] = @Id
	
	RETURN @row_count
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('Projects') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Projects
GO

CREATE TABLE Projects
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50)
)
go

INSERT INTO Projects(Name) VALUES ('Project 1');
GO

INSERT INTO Projects(Name) VALUES ('Project 2');
GO

IF OBJECT_ID ( 'get_projects', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_projects;
GO

CREATE PROCEDURE get_projects
AS
	SELECT Id, Name FROM Projects
GO

IF OBJECT_ID('create_project', 'p') IS NOT NULL 
    DROP PROCEDURE create_project;
GO

CREATE PROCEDURE create_project
    @Name NVARCHAR(50)
AS
	INSERT INTO Projects([Name]) VALUES(@Name)
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('update_project', 'p') IS NOT NULL 
    DROP PROCEDURE update_project;
GO

CREATE PROCEDURE update_project
    @Id INT,
	@Name NVARCHAR(50)
AS
	UPDATE Projects SET 
		[Name] = @Name
	WHERE Id = @Id
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('remove_project', 'p') IS NOT NULL 
    DROP PROCEDURE remove_project;
GO

CREATE PROCEDURE remove_project
    @Id INT
AS
	DECLARE @row_count INT

	DELETE FROM Projects WHERE Id = @Id
	SET @row_count = @@ROWCOUNT;

	UPDATE Releases SET [Project_id] = NULL WHERE [Project_id] = @Id
	UPDATE Iterations SET [Project_id] = NULL WHERE [Project_id] = @Id
	UPDATE Tasks SET [Project_id] = NULL WHERE [Project_id] = @Id
	UPDATE UserStories SET [Project_id] = NULL WHERE [Project_id] = @Id

	RETURN @row_count
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('StatesGroups') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE StatesGroups
GO

CREATE TABLE StatesGroups
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50)
)
go

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('States') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE States
GO

CREATE TABLE States
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50),
	[Group_id] INT
)
go

INSERT INTO StatesGroups(Name) VALUES ('Task and UserStory status');
GO
INSERT INTO States(Name, Group_id) VALUES ('Defined',1), ('Completed',1), ('In progress',1);
GO
INSERT INTO StatesGroups(Name) VALUES ('Iteration status');
GO
INSERT INTO States(Name, Group_id) VALUES ('Planning',2), ('Committed',2), ('Accepted',2);
GO
INSERT INTO StatesGroups(Name) VALUES ('Release status');
GO
INSERT INTO States(Name, Group_id) VALUES ('Planning',3), ('Active',3), ('Accepted',3);
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('Releases') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Releases
GO

CREATE TABLE Releases
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Project_id] INT,
	[Start_date] DATE,
	[End_date] DATE,
	[State_id] INT,
	[Plan_estimate] INT
)
go

INSERT INTO Releases(Project_id, Start_date, End_date, State_id, Plan_Estimate) VALUES (1, '20050507', '20050707', 8, 200);
GO
INSERT INTO Releases(Project_id, Start_date, End_date, State_id, Plan_Estimate) VALUES (2, '20070301', '20080101', 7, 1000);
GO

IF OBJECT_ID ( 'get_releases', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_releases;
GO

CREATE PROCEDURE get_releases
AS
	SELECT R.Id, R.Project_id, P.Name as 'Project', R.Start_date, R.End_date, R.State_id, S.Name as 'State', R.Plan_estimate FROM Releases R LEFT OUTER JOIN Projects P on R.Project_id = P.Id LEFT OUTER JOIN States S on R.State_id = S.Id
GO

IF OBJECT_ID('remove_release', 'p') IS NOT NULL 
    DROP PROCEDURE remove_release;
GO

CREATE PROCEDURE remove_release
    @Id INT
AS
	DECLARE @row_count INT
	DELETE FROM Releases WHERE Id = @Id
	SET @row_count = @@ROWCOUNT;
	
	UPDATE Iterations SET [Release_id] = NULL WHERE [Release_id] = @Id
	
	RETURN @row_count
GO

IF OBJECT_ID('update_release', 'p') IS NOT NULL 
    DROP PROCEDURE update_release;
GO

CREATE PROCEDURE update_release
	@Id INT,
	@Project_id INT,
	@Start_date DATE,
	@End_date DATE,
	@State_id INT,
	@Plan_estimate INT
AS
	UPDATE Releases SET 
		[Project_id] = @Project_id, 
		[Start_date] = @Start_date, 
		[End_date] = @End_date,
		[State_id] = @State_id,
		[Plan_estimate] = @Plan_estimate
	WHERE Id = @Id
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('add_release', 'p') IS NOT NULL 
    DROP PROCEDURE add_release;
GO

CREATE PROCEDURE add_release
	@Project_id INT,
	@Start_date DATE,
	@End_date DATE,
	@State_id INT,
	@Plan_estimate INT
AS
	Insert into Releases([Project_id], [Start_date], [End_date], [State_id], [Plan_estimate]) VALUES (@Project_id, @Start_date, @End_date, @State_id, @Plan_estimate)
	RETURN @@ROWCOUNT
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('Iterations') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Iterations
GO

CREATE TABLE Iterations
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Project_id] INT,
	[Start_date] DATE,
	[End_date] DATE,
	[State_id] INT,
	[Plan_estimate] INT,
	[Release_id] INT
)
go

INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (1, '20050507', '20050601', 6, 80, 1);
GO
INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (1, '20050602', '20050620', 4, 65, 1);
GO
INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (1, '20050621', '20050707', 4, 55, 1);
GO

INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (2, '20070301', '20070501', 4, 280, 2);
GO
INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (2, '20070502', '20070601', 4, 70, 2);
GO
INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (2, '20070602', '20070801', 4, 250, 2);
GO
INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (2, '20070802', '20071015', 4, 310, 2);
GO
INSERT INTO Iterations(Project_id, Start_date, End_date, State_id, Plan_Estimate, Release_id) VALUES (2, '20071016', '20080101', 4, 90, 2);
GO


IF OBJECT_ID ( 'get_iterations', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_iterations;
GO

CREATE PROCEDURE get_iterations
AS
	SELECT I.Id, I.Project_id, P.Name as 'Project', I.Start_date, I.End_date, I.State_id, S.Name as 'State', I.Plan_estimate, Release_id FROM Iterations I LEFT OUTER JOIN Projects P on I.Project_id = P.Id LEFT OUTER JOIN States S on I.State_id = S.Id
GO

IF OBJECT_ID ( 'get_iterations_list', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_iterations_list;
GO

CREATE PROCEDURE get_iterations_list
AS
	SELECT Id FROM Iterations
GO

IF OBJECT_ID('remove_iteration', 'p') IS NOT NULL 
    DROP PROCEDURE remove_iteration;
GO

CREATE PROCEDURE remove_iteration
    @Id INT
AS
	DECLARE @row_count INT
	DELETE FROM Iterations WHERE Id = @Id
	SET @row_count = @@ROWCOUNT;
	
	UPDATE UserStories SET [Iteration_id] = NULL WHERE [Iteration_id] = @Id
	UPDATE ChartData SET [Iteration_id] = NULL WHERE [Iteration_id] = @Id
	
	RETURN @row_count
GO

IF OBJECT_ID('update_iteration', 'p') IS NOT NULL 
    DROP PROCEDURE update_iteration;
GO

CREATE PROCEDURE update_iteration
	@Id INT,
	@Project_id INT,
	@Start_date DATE,
	@End_date DATE,
	@State_id INT,
	@Plan_estimate INT,
	@Release_id INT
AS
	UPDATE Iterations SET 
		[Project_id] = @Project_id, 
		[Start_date] = @Start_date, 
		[End_date] = @End_date,
		[State_id] = @State_id,
		[Plan_estimate] = @Plan_estimate,
		[Release_id] = @Release_id
	WHERE Id = @Id
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('add_iteration', 'p') IS NOT NULL 
    DROP PROCEDURE add_iteration;
GO

CREATE PROCEDURE add_iteration
	@Project_id INT,
	@Start_date DATE,
	@End_date DATE,
	@State_id INT,
	@Plan_estimate INT,
	@Release_id INT
AS
	Insert into Iterations([Project_id], [Start_date], [End_date], [State_id], [Plan_estimate], [Release_id]) VALUES (@Project_id, @Start_date, @End_date, @State_id, @Plan_estimate, @Release_id)
	RETURN @@ROWCOUNT
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('UserStories') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE UserStories
GO

CREATE TABLE UserStories
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50),
	[Owner_id] INT,
	[Project_id] INT,
	[Description] NVARCHAR(200),
	[State_id] INT,
	[Estimate] INT,
	[Actuals] INT,
	[To_do] INT,
	[Plan_estimate] INT,
	[Iteration_id] INT,
	[Notes] NVARCHAR(200)
)
go

INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Implement specification', 1, 1, 2, 40, 35, 5, 30, 1);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project design', 2, 1, 2, 40, 39, 1, 35, 1);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project Model and UI Coding', 1, 1, 2, 35, 25, 2, 35, 2);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project Controller Coding', 2, 1, 2, 30, 35, 4, 30, 2);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project Testing', 2, 1, 3, 30, 23, 3, 30, 3);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project Installation', 1, 1, 1, 25, 0, 25, 23, 3);
GO

INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Implement specification', 2, 2, 1, 280, 0, 280, 280, 4);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project design', 2, 2, 1, 70, 0, 70, 80, 5);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project coding', 1, 2, 1, 250, 0, 250, 260, 6);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project testing', 2, 2, 1, 310, 0, 310, 300, 7);
GO
INSERT INTO UserStories(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, Plan_Estimate, Iteration_id) VALUES ('Project installation', 1, 2, 1, 90, 0, 90, 80, 8);
GO

IF OBJECT_ID ( 'get_user_stories', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_user_stories;
GO

CREATE PROCEDURE get_user_stories
AS
	SELECT US.Id, US.Name, US.Owner_id, U.Name as 'Owner', US.Project_id, P.Name as 'Project', US.Description, US.State_id, S.Name as 'State', US.Estimate, US.Actuals, US.To_do, US.Plan_estimate, US.Iteration_id, US.Notes FROM UserStories US LEFT OUTER JOIN Users U on US.Owner_id = U.Id LEFT OUTER JOIN Projects P on US.Project_id = P.Id LEFT OUTER JOIN States S on US.State_id = S.Id
GO

IF OBJECT_ID ( 'get_user_stories_list', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_user_stories_list;
GO

CREATE PROCEDURE get_user_stories_list
AS
	SELECT Id, Name FROM UserStories
GO

IF OBJECT_ID('remove_user_story', 'p') IS NOT NULL 
    DROP PROCEDURE remove_user_story;
GO

CREATE PROCEDURE remove_user_story
    @Id INT
AS
	DECLARE @row_count INT
	DELETE FROM UserStories WHERE Id = @Id
	SET @row_count = @@ROWCOUNT;
	
	UPDATE Tasks SET [User_story_id] = NULL WHERE [User_story_id] = @Id
	
	RETURN @row_count
GO

IF OBJECT_ID('update_user_story', 'p') IS NOT NULL 
    DROP PROCEDURE update_user_story;
GO

CREATE PROCEDURE update_user_story
    @Id INT,
	@Name NVARCHAR(50),
	@Owner_id INT,
	@Project_id INT,
	@Description NVARCHAR(200),
	@State_id INT,
	@Estimate INT,
	@Actuals INT,
	@To_do INT,
	@Plan_estimate INT,
	@Iteration_id INT,
	@Notes NVARCHAR(200)
AS
	UPDATE UserStories SET 
		[Name] = @Name, 
		[Owner_id] = @Owner_id, 
		[Project_id] = @Project_id, 
		[Description] = @Description, 
		[State_id] = @State_id,
		[Estimate] = @Estimate,
		[Actuals] = @Actuals,
		[To_do] = @To_do,
		[Plan_estimate] = @Plan_estimate,
		[Iteration_id] = @Iteration_id,
		[Notes] = @Notes 
	WHERE Id = @Id
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('add_user_story', 'p') IS NOT NULL 
    DROP PROCEDURE add_user_story;
GO

CREATE PROCEDURE add_user_story
	@Name NVARCHAR(50),
	@Owner_id INT,
	@Project_id INT,
	@Description NVARCHAR(200),
	@State_id INT,
	@Estimate INT,
	@Actuals INT,
	@To_do INT,
	@Plan_estimate INT,
	@Iteration_id INT,
	@Notes NVARCHAR(200)
AS
	Insert into UserStories([Name], [Owner_id], [Project_id], [Description], [State_id], [Estimate], [Actuals], [To_do], [Plan_estimate], [Iteration_id], [Notes]) VALUES (@Name, @Owner_id, @Project_id, @Description, @State_id, @Estimate, @Actuals, @To_do, @Plan_estimate, @Iteration_id, @Notes)
	RETURN @@ROWCOUNT
GO


IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('Tasks') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Tasks
GO

CREATE TABLE Tasks
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50),
	[Owner_id] INT,
	[Project_id] INT,
	[Description] NVARCHAR(200),
	[State_id] INT,
	[Estimate] INT,
	[Actuals] INT,
	[To_do] INT,
	[User_story_id] INT,
	[Notes] NVARCHAR(200),
	[Modified_date] DATE
)
go

IF OBJECT_ID ( 'get_tasks', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_tasks;
GO

CREATE PROCEDURE get_tasks
AS
	SELECT T.Id, T.Name, T.Owner_id, U.Name as 'Owner', T.Project_id, P.Name as 'Project', T.Description, T.State_id, S.Name as 'State', T.Estimate, T.Actuals, T.To_do, T.User_story_id, US.Name as 'User Story', T.Notes FROM Tasks T LEFT OUTER JOIN Users U on T.Owner_id = U.Id LEFT OUTER JOIN Projects P on T.Project_id = P.Id LEFT OUTER JOIN States S on T.State_id = S.Id LEFT OUTER JOIN UserStories US on T.User_story_id = US.Id
GO

IF OBJECT_ID('remove_task', 'p') IS NOT NULL 
    DROP PROCEDURE remove_task;
GO

CREATE PROCEDURE remove_task
    @Id INT
AS
	DECLARE @row_count INT
	DELETE FROM Tasks WHERE Id = @Id
	SET @row_count = @@ROWCOUNT;
	
	UPDATE Documents SET [Task_id] = NULL WHERE [Task_id] = @Id
	
	RETURN @row_count
GO

IF OBJECT_ID('update_task', 'p') IS NOT NULL 
    DROP PROCEDURE update_task;
GO

CREATE PROCEDURE update_task
    @Id INT,
	@Name NVARCHAR(50),
	@Owner_id INT,
	@Project_id INT,
	@Description NVARCHAR(200),
	@State_id INT,
	@Estimate INT,
	@Actuals INT,
	@To_do INT,
	@User_story_id INT,
	@Notes NVARCHAR(200)
AS
	UPDATE Tasks SET 
		[Name] = @Name, 
		[Owner_id] = @Owner_id, 
		[Project_id] = @Project_id, 
		[Description] = @Description, 
		[State_id] = @State_id,
		[Estimate] = @Estimate,
		[Actuals] = @Actuals,
		[To_do] = @To_do,
		[User_story_id] = @User_story_id,
		[Notes] = @Notes,
		[Modified_date] = GETDATE()
	WHERE Id = @Id
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('add_task', 'p') IS NOT NULL 
    DROP PROCEDURE add_task;
GO

CREATE PROCEDURE add_task
	@Name NVARCHAR(50),
	@Owner_id INT,
	@Project_id INT,
	@Description NVARCHAR(200),
	@State_id INT,
	@Estimate INT,
	@Actuals INT,
	@To_do INT,
	@User_story_id INT,
	@Notes NVARCHAR(200)
AS
	Insert into Tasks([Name], [Owner_id], [Project_id], [Description], [State_id], [Estimate], [Actuals], [To_do], [User_story_id], [Notes], [Modified_date]) 
	VALUES (@Name, @Owner_id, @Project_id, @Description, @State_id, @Estimate, @Actuals, @To_do, @User_story_id, @Notes, GETDATE())
	RETURN @@IDENTITY
GO


--INSERT INTO Tasks(Name, Estimate) VALUES ('DE8876: Create DB Scripts', 16);
--GO

--INSERT INTO Tasks(Name, Estimate) VALUES ('TA776: Change wording', 4);
--GO

INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Task 1', 1, 1, 2, 20, 17, 0, 1, '20050525');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Task 2', 1, 1, 2, 20, 18, 0, 1, '20050527');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Project design', 2, 1, 2, 40, 39, 1, 2, '20050529');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Project model coding', 1, 1, 2, 20, 15, 0, 3, '20050615');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Project UI coding', 1, 1, 2, 15, 10, 0, 3, '20050617');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Project controller', 2, 1, 2, 30, 35, 4, 4, '20050616');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Unit testing', 2, 1, 3, 15, 10, 2, 5, '20050701');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Other testing', 2, 1, 3, 15, 13, 1, 5, '20050703');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Install project', 1, 1, 1, 25, 0, 25, 6, '20050705');
GO

INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Specify functional requirements', 2, 2, 1, 140, 0, 140, 7, '20070302');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Specify non-functional requirements', 2, 2, 1, 140, 0, 140, 7, '20070303');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Use-case diagram', 2, 2, 1, 35, 0, 35, 8, '20070503');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Sequence diagram', 2, 2, 1, 35, 0, 35, 8, '20070505');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Model and logic coding', 1, 2, 1, 130, 0, 130, 9, '20070603');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('UI coding', 1, 2, 1, 120, 0, 120, 9, '20070606');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Testing', 2, 2, 1, 150, 0, 150, 10, '20070804');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Bug fixing', 2, 2, 1, 160, 0, 160, 10, '20070806');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Install project', 1, 2, 1, 20, 0, 20, 11, '20071017');
GO
INSERT INTO Tasks(Name, Owner_id, Project_id, State_id, Estimate, Actuals, To_do, User_story_id, Modified_date) VALUES ('Personnel training', 1, 2, 1, 70, 0, 70, 11, '20071020');
GO

IF OBJECT_ID ( 'get_team_status', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_team_status;
GO

CREATE PROCEDURE get_team_status
AS
	SELECT T.Id, T.Name, T.Owner_id, U.Name as 'Owner', T.Project_id, P.Name as 'Project', T.State_id, S.Name as 'State', T.Estimate, T.Actuals, T.To_do FROM Tasks T LEFT OUTER JOIN Users U ON T.Owner_id = U.Id LEFT OUTER JOIN Projects P ON T.Project_id = P.Id LEFT OUTER JOIN States S on T.State_id = S.Id
	UNION ALL
	SELECT US.Id, US.Name, US.Owner_id, U.Name as 'Owner', US.Project_id, P.Name as 'Project', US.State_id, S.Name as 'State', US.Estimate, US.Actuals, US.To_do FROM UserStories US LEFT OUTER JOIN Users U ON US.Owner_id = U.Id LEFT OUTER JOIN Projects P ON US.Project_id = P.Id LEFT OUTER JOIN States S on US.State_id = S.Id
GO

IF OBJECT_ID ( 'get_team_status_with_filter', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_team_status_with_filter;
GO

CREATE PROCEDURE get_team_status_with_filter
	@Owner_id INT,
	@Iteration_id INT,
	@Release_id INT
AS
	DECLARE @Filter_Tasks NVARCHAR(max)
	DECLARE @Filter_UserStories NVARCHAR(max)
	DECLARE @Filter_Other NVARCHAR(max)
	SET @Filter_Tasks = '';
	SET @Filter_UserStories = '';
	SET @Filter_Other = '';
	IF (@Owner_id <> 0) 
	BEGIN
		SET @Filter_Tasks = 'WHERE T.Owner_id = ' + CAST(@Owner_id as NVARCHAR(10));
		SET @Filter_UserStories = 'WHERE US.Owner_id = ' + CAST(@Owner_id as NVARCHAR(10));
	END
	IF (@Iteration_id <> 0)
	BEGIN
		IF (@Filter_Tasks != '') SET @Filter_Other = ' AND US.Iteration_id = ' + CAST(@Iteration_id as NVARCHAR(10));
		ELSE SET @Filter_Other = 'WHERE US.Iteration_id = ' + CAST(@Iteration_id as NVARCHAR(10));
	END
	IF (@Release_id <> 0)
	BEGIN
		IF (@Filter_Tasks != '') SET @Filter_Other = @Filter_Other + ' AND I.Release_id = ' + CAST(@Release_id as NVARCHAR(10));
		ELSE IF (@Filter_Other != '') SET @Filter_Other = @Filter_Other + ' AND I.Release_id = ' + CAST(@Release_id as NVARCHAR(10));
		ELSE SET @Filter_Other = 'WHERE I.Release_id = ' + CAST(@Release_id as NVARCHAR(10));
	END
	DECLARE @QUERY NVARCHAR(max)
	SET @QUERY = 'SELECT T.Id, T.Name, T.Owner_id, U.Name as "Owner", T.Project_id, P.Name as "Project", T.State_id, S.Name as "State", T.Estimate, T.Actuals, T.To_do FROM Tasks T LEFT OUTER JOIN Users U ON T.Owner_id = U.Id LEFT OUTER JOIN Projects P ON T.Project_id = P.Id LEFT OUTER JOIN States S on T.State_id = S.Id LEFT OUTER JOIN UserStories US ON T.User_story_id = US.Id LEFT OUTER JOIN Iterations I ON US.Iteration_id = I.Id ' + @Filter_Tasks + @Filter_Other + 
	' UNION ALL
	SELECT US.Id, US.Name, US.Owner_id, U.Name as "Owner", US.Project_id, P.Name as "Project", US.State_id, S.Name as "State", US.Estimate, US.Actuals, US.To_do FROM UserStories US LEFT OUTER JOIN Users U ON US.Owner_id = U.Id LEFT OUTER JOIN Projects P ON US.Project_id = P.Id LEFT OUTER JOIN States S on US.State_id = S.Id LEFT OUTER JOIN Iterations I ON US.Iteration_id = I.Id ' + @Filter_UserStories + @Filter_Other;
	EXEC(@QUERY)
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('Documents') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Documents
GO

CREATE TABLE Documents
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Task_id] INT,
	[Name] NVARCHAR(50),
	[Url] NVARCHAR(50)
)
go

IF OBJECT_ID('add_document', 'p') IS NOT NULL 
    DROP PROCEDURE add_document;
GO

CREATE PROCEDURE add_document
	@Task_id INT,
	@Name NVARCHAR(50),
	@Url NVARCHAR(50)
AS
	Insert into Documents([Task_id], [Name], [Url]) VALUES (@Task_id, @Name, @Url)
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('get_documents', 'p') IS NOT NULL 
    DROP PROCEDURE get_documents;
GO

CREATE PROCEDURE get_documents
	@Task_id INT
AS
	SELECT Id, Task_id, Name, Url FROM Documents WHERE Task_id = @Task_id
GO

IF OBJECT_ID('remove_documents', 'p') IS NOT NULL 
    DROP PROCEDURE remove_documents;
GO

CREATE PROCEDURE remove_documents
	@Task_id INT
AS
	DELETE FROM Documents WHERE Task_id = @Task_id
	RETURN @@ROWCOUNT
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('Logs') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Logs
GO

CREATE TABLE Logs
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[User_id] INT,
	[Action] NVARCHAR(200)
)
GO

INSERT INTO Logs(User_id, Action) VALUES (1, 'Something added');
GO

INSERT INTO Logs(User_id, Action) VALUES (1, 'Something added 2');
GO

IF OBJECT_ID ( 'get_logs', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_logs;
GO

CREATE PROCEDURE get_logs
AS
	SELECT L.Id, L.User_id, U.Name, L.Action FROM Logs L LEFT OUTER JOIN Users U on L.User_id = U.Id
GO

IF OBJECT_ID('remove_logs', 'p') IS NOT NULL 
    DROP PROCEDURE remove_logs;
GO

CREATE PROCEDURE remove_logs
AS
	DELETE FROM Logs
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID('add_logs', 'p') IS NOT NULL 
    DROP PROCEDURE add_logs;
GO

CREATE PROCEDURE add_logs
	@User_id INT,
	@Action NVARCHAR(200)
AS
	Insert into Logs([User_id], [Action]) VALUES (@User_id, @Action)
	RETURN @@ROWCOUNT
GO

IF OBJECT_ID ( 'get_burnupdown', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_burnupdown;
GO
	
CREATE PROCEDURE get_burnupdown
	@Iteration_id INT
AS
	SELECT t.Modified_date, SUM(t.To_do) AS TO_DO, SUM(t.Actuals) AS ACTUALS
	FROM Tasks t
	LEFT OUTER JOIN UserStories us
	ON t.User_story_id = us.Id
	LEFT OUTER JOIN Iterations it
	ON us.Iteration_id = it.Id
	WHERE it.Id = @Iteration_id AND
		(t.Modified_date BETWEEN it.Start_date AND it.End_date)	
	GROUP BY t.Modified_date
GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
	WHERE id = object_id('ChartData') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE ChartData
GO

CREATE TABLE ChartData
(
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[Iteration_id] INT,
	[Actuals] INT,
	[To_do] INT,
	[Modified_date] DATE
)
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 0, 250, '2012-08-01')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 30, 220, '2012-08-02')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 60, 190, '2012-08-03')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 80, 150, '2012-08-04')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 110, 120, '2012-08-05')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 140, 100, '2012-08-06')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 180, 80, '2012-08-07')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 220, 40, '2012-08-08')
GO

INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
VALUES (1, 250, 0, '2012-08-08')
GO

IF OBJECT_ID ( 'save_chartdata', 'P' ) IS NOT NULL 
    DROP PROCEDURE save_chartdata;
GO
	
CREATE PROCEDURE save_chartdata
AS
	DECLARE @TodayInsertedCount INT
	SELECT @TodayInsertedCount = COUNT(*) 
		FROM ChartData 
		WHERE CONVERT(CHAR(10),[Modified_date],105) = CONVERT(CHAR(10),GETDATE(),105)
	--PRINT @TodayInsertedCount

	IF @TodayInsertedCount = 0
	BEGIN
		INSERT INTO ChartData([Iteration_id], [Actuals], [To_do],[Modified_date])
		SELECT it.Id, SUM(t.[Actuals]) AS Actuals, SUM(t.[To_do]) AS To_do, GETDATE() AS [Modified_date]
		FROM Iterations it
		RIGHT OUTER JOIN UserStories us
		ON it.[Id] = us.[Iteration_id]
		RIGHT OUTER JOIN Tasks t
		ON us.[Id] = t.[User_story_id]
		WHERE CONVERT(CHAR(10),it.[Start_date],105) <= CONVERT(CHAR(10),GETDATE(),105) 
			AND CONVERT(CHAR(10),it.[End_date],105) >= CONVERT(CHAR(10),GETDATE(),105)
		GROUP BY it.[Id]
	END
	RETURN 0
GO

IF OBJECT_ID ( 'get_chartdata', 'P' ) IS NOT NULL 
    DROP PROCEDURE get_chartdata;
GO
	
CREATE PROCEDURE get_chartdata
	@Iteration_id INT
AS
	SELECT [Modified_date], To_do, Actuals
	FROM ChartData
	WHERE Iteration_id = @Iteration_id
GO
